import xlwt

from configuration_files.Connect_files import DbConnect

conn = DbConnect().system_db()
cursor = conn.cursor()


def save_st_excel(info_list, filepath, date_section, word):
    """制作办登、受通Excel"""
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("sheet1")
    sheet.write(0, 0, '编号')
    sheet.write(0, 1, '申请号')
    if word == "受通费减":
        sheet.write(0, 2, '申请日')
    else:
        sheet.write(0, 2, '发文日')
    sheet.write(0, 3, '定稿名称')
    sheet.write(0, 4, '专利权人')
    sheet.write(0, 5, '联络人')
    sheet.write(0, 6, '费用类型')
    sheet.write(0, 7, '费用减缴合计')
    sheet.write(0, 8, '类型')
    sheet.write(0, 9, '备注')
    sheet.write(0, 10, '申报单位')
    sheet.write(0, 11, '联络人电话')
    sheet.write(0, 12, '联络人邮箱')
    sheet.write(0, 13, '客服')
    if len(info_list) > 0:
        for i in range(len(info_list)):
            sheet.write(i + 1, 0, info_list[i]['order_number'])
            sheet.write(i + 1, 1, info_list[i]['sqh'])
            sheet.write(i + 1, 2, info_list[i]['fwr'])
            sheet.write(i + 1, 3, info_list[i]['name'])
            sheet.write(i + 1, 4, info_list[i]['user'])
            sheet.write(i + 1, 5, info_list[i]['lxr'])
            sheet.write(i + 1, 6, info_list[i]['yjf'])
            sheet.write(i + 1, 7, info_list[i]['money'])
            sheet.write(i + 1, 8, info_list[i]['type'])
            sheet.write(i + 1, 9, info_list[i]['marks'])
            sheet.write(i + 1, 10, info_list[i]['inst'])
            sheet.write(i + 1, 11, info_list[i]['lxr_tel'])
            sheet.write(i + 1, 12, info_list[i]['email'])
            sheet.write(i + 1, 13, info_list[i]['process'])
        workbook.save(r'{}/{}/{}{}{}件.xls'.format(filepath, word, date_section, word, len(info_list)))


def get_agentpay_content(sqh):
    sql1 = "select consumer_pay_status,consumer_pay_date,process_id from finals where application_number='ZL{}'".format(
        sqh)
    cursor.execute(sql1)
    content = cursor.fetchall()
    return content


def get_jf_state(content, item):
    if len(content) > 0:
        if content[0][0] == 1:
            item['status'] = '已缴费'
        elif content[0][0] == 2:
            item['status'] = '未缴费'
        elif content[0][0] == 3:
            item['status'] = '已退款'
        else:
            item['status'] = '其它-{}'.format(content[0][0])
    else:
        item['status'] = '-'
    return item


def get_content3(agent_id):
    sql = "select name,type from users where id = '{}'".format(agent_id)
    cursor.execute(sql)
    content = cursor.fetchall()
    return content


def get_agent_state(sqh, item):
    sql = "select agent_id from finals where application_number like '%{}%'".format(sqh)
    cursor.execute(sql)
    agent_id = cursor.fetchall()
    if len(agent_id) > 0:
        info = get_content3(agent_id[0][0])
        if len(info) > 0:
            status = info[0][1]
            if status == '离职':
                item['agent'] = '原代理人为{}已离职，请更换代理人！'.format(info[0][0])
            else:
                item['agent'] = info[0][0]
        else:
            item['agent'] = ''
    else:
        item['agent'] = ""
    return item


def save_zs_excel(info_list, filepath, date_section, word):
    """制作证书Excel"""
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("sheet1")
    sheet.write(0, 0, '编号')
    sheet.write(0, 1, '发文日')
    sheet.write(0, 2, '申请日')
    sheet.write(0, 3, '定稿名称')
    sheet.write(0, 4, '专利权人')
    sheet.write(0, 5, '联络人')
    sheet.write(0, 6, '类型')
    sheet.write(0, 7, '申报单位')
    sheet.write(0, 8, '联络人电话')
    sheet.write(0, 9, '联络人邮箱')
    sheet.write(0, 10, '客服')
    if len(info_list) > 0:
        for i in range(len(info_list)):
            sheet.write(i + 1, 0, info_list[i]['order_number'])
            sheet.write(i + 1, 1, info_list[i]['sqh'])
            sheet.write(i + 1, 2, info_list[i]['fwr'])
            sheet.write(i + 1, 3, info_list[i]['name'])
            sheet.write(i + 1, 4, info_list[i]['user'])
            sheet.write(i + 1, 5, info_list[i]['lxr'])
            sheet.write(i + 1, 6, info_list[i]['type'])
            sheet.write(i + 1, 7, info_list[i]['inst'])
            sheet.write(i + 1, 8, info_list[i]['lxr_tel'])
            sheet.write(i + 1, 9, info_list[i]['email'])
            sheet.write(i + 1, 10, info_list[i]['process'])
        workbook.save(r'{}/{}/{}{}{}件.xls'.format(filepath, word, date_section, word, len(info_list)))


def save_bz_bh_excel(info_list, filepath, date_section, word):
    """制作补正答复Excel"""
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("sheet1")
    if word == "补正答复":
        sheet.write(0, 0, '意见下发时间')
        sheet.write(0, 1, '原案件编号')
        sheet.write(0, 2, '案件编号')
        sheet.write(0, 3, '第一发明人')
        sheet.write(0, 4, '案件名称')
        sheet.write(0, 5, '专利号')
        sheet.write(0, 6, '状态')
        sheet.write(0, 7, '代理人')
        sheet.write(0, 8, '缴费状态')
        sheet.write(0, 9, '备注')
        sheet.write(0, 10, '最新状态')
        sheet.write(0, 11, '专属顾问')
        sheet.write(0, 12, "接收端口")
        if len(info_list) > 0:
            for i in range(len(info_list)):
                sheet.write(i + 1, 0, info_list[i]['fwr'])
                sheet.write(i + 1, 2, info_list[i]['order_number'])
                sheet.write(i + 1, 3, info_list[i]['fmr'])
                sheet.write(i + 1, 4, info_list[i]['name'])
                sheet.write(i + 1, 5, info_list[i]['sqh'])
                sheet.write(i + 1, 6, info_list[i]['aj_state'])
                sheet.write(i + 1, 7, info_list[i]['agent'])
                sheet.write(i + 1, 8, info_list[i]['status'])
                sheet.write(i + 1, 11, info_list[i]['process'])
                sheet.write(i + 1, 12, info_list[i]['inst'])
            workbook.save(r'{}/{}/{}{}{}件.xls'.format(filepath, word, date_section, word, len(info_list)))
    else:
        sheet.write(0, 0, '编号')
        sheet.write(0, 1, '发文日')
        sheet.write(0, 2, '申请号')
        sheet.write(0, 3, '定稿名称')
        sheet.write(0, 4, '专利权人')
        sheet.write(0, 5, '第一发明人')
        sheet.write(0, 6, '联络人')
        sheet.write(0, 7, '联络人电话')
        sheet.write(0, 8, '专利类型')
        sheet.write(0, 9, '状态')
        sheet.write(0, 10, '代理人')
        sheet.write(0, 11, '缴费状态')
        sheet.write(0, 12, "客服")
        if len(info_list) > 0:
            for i in range(len(info_list)):
                sheet.write(i + 1, 0, info_list[i]['order_number'])
                sheet.write(i + 1, 1, info_list[i]['fwr'])
                sheet.write(i + 1, 2, info_list[i]['sqh'])
                sheet.write(i + 1, 3, info_list[i]['name'])
                sheet.write(i + 1, 4, info_list[i]['user'])
                sheet.write(i + 1, 5, info_list[i]['fmr'])
                sheet.write(i + 1, 6, info_list[i]['lxr'])
                sheet.write(i + 1, 7, info_list[i]['lxr_tel'])
                sheet.write(i + 1, 8, info_list[i]['type'])
                sheet.write(i + 1, 9, info_list[i]['aj_state'])
                sheet.write(i + 1, 10, info_list[i]['agent'])
                sheet.write(i + 1, 11, info_list[i]['status'])
                sheet.write(i + 1, 12, info_list[i]['process'])
            workbook.save(r'{}/{}/{}{}{}件.xls'.format(filepath, word, date_section, word, len(info_list)))
